/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import model.dto.PostCategory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import model.util.DBUtil;

/**
 *
 * @author PhucAn
 */
public class PostCategoryDAO {

    Connection conn = null;
    Logger log = Logger.getLogger(PostCategoryDAO.class);
    private final String sqlReadAll = "select * from Post_Category";
    private final String sqlReadById = "select * from Post_Category where id=?";
    private final String sqlCreate = "insert into post_category([name],parent_id) values(?,?)";
    private final String sqlUpdate = "update post_category set [name]=?, parent_id=? where id=?";
    private final String sqlDelete = "delete from post_category where id=?";

    public PostCategory getCategory(int id) {
        PostCategory cate = new PostCategory();
        conn = DBUtil.getConnection();
        if (conn == null) {
            return cate;
        }
        try {
            PreparedStatement ps = conn.prepareStatement(sqlReadById);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                cate.setId(rs.getInt(1));
                cate.setName(rs.getString(2));
                cate.setParentId(rs.getInt(3));
            }
            DBUtil.disconnect();
        } catch (SQLException ex) {
            log.info(ex.getMessage());
        } catch (Exception ex) {
            log.info(ex.getMessage());
        }
        return cate;
    }

    public ArrayList<PostCategory> getAllCategories() {
        ArrayList<PostCategory> list = new ArrayList<PostCategory>();
        conn = DBUtil.getConnection();
        if (conn == null) {
            return null;
        }
        try {
            PreparedStatement ps = conn.prepareStatement(sqlReadAll);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                PostCategory cate = new PostCategory();
                cate.setId(rs.getInt(1));
                cate.setName(rs.getString(2));
                cate.setParentId(rs.getInt(3));
                list.add(cate);

            }
            DBUtil.disconnect();
        } catch (SQLException ex) {
            log.info(ex.getMessage());
        } catch (Exception ex) {
            log.info(ex.getMessage());
        }
        return list;
    }

    public int update(PostCategory category) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setString(1, category.getName());
            ps.setInt(2, category.getParentId());
            ps.setInt(3, category.getId());
            effectedRows = ps.executeUpdate();
        } catch (SQLException ex) {
            log.info("Error: " + ex.getErrorCode() + ex.getMessage());
        } catch (Exception ex) {
            log.info(ex.getMessage());
        }
        return effectedRows;
    }

    public int create(PostCategory category) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setString(1, category.getName());
            ps.setInt(2, category.getParentId());
            effectedRows = ps.executeUpdate();
        } catch (SQLException ex) {
            log.info("Error: " + ex.getErrorCode() + ex.getMessage());
        } catch (Exception ex) {
            log.info(ex.getMessage());
        }
        return effectedRows;
    }

    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
        } catch (SQLException ex) {
            log.info("Error: " + ex.getErrorCode() + ex.getMessage());
        } catch (Exception ex) {
            log.info(ex.getMessage());
        }
        return effectedRows;
    }
}
